#!/usr/bin/perl -w

use strict;
use v5.14;
use utf8;

use Spreadsheet::ParseExcel;
use JSON;

binmode(STDIN,":utf8");
binmode(STDOUT,":utf8");
binmode(STDERR,":utf8");

@ARGV == 1 || die "You must provide input file.";
my $file = $ARGV[0];
my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse($file);

#读取sanitation.json
open my $fh, '<', './data/sanitation.json';
read $fh, my $san, -s $fh;
my $sanitation = decode_json $san;
close $fh;

# all the data will be in this list
my @list = ();
$workbook || die $parser->error();
for my $worksheet ($workbook->worksheets()) {
    my $sheetname = $worksheet->get_name;
    $sheetname =~ s/龙北/龙川北苑/;
    $sheetname =~ s/龙南/龙川南苑/;

    my ($row_min, $row_max) = $worksheet->row_range();
    my ($col_min, $col_max) = $worksheet->col_range();

    #从第2行抽取寝室号，存在问题，检查时间所在列号
    my ($roomIdx, $probIdx, $dateIdx);
    foreach (0..$col_max) {
        my $cell = $worksheet->get_cell(1, $_);
        break if(!$cell);
        my $c = $cell->value();
        if($c eq '寝室号') {
            $roomIdx = $_;
        }
        if($c =~ /^存在问题.*/) {
            $probIdx = $_;
        }
        if($c eq '检查时间') {
            $dateIdx = $_;
        }
    }
    # say $sheetname, ' ', $roomIdx, ' ', $probIdx, ' ', $dateIdx;

    foreach (2..$row_max) {
        next if !$worksheet->get_cell($_,0);
        my $room = $worksheet->get_cell($_, $roomIdx)->value();
        my $prob = $worksheet->get_cell($_, $probIdx)->value();
        my $date = $worksheet->get_cell($_, $dateIdx)->value();
        #    say $room, ' ', $prob, ' ', $date;
        next if length $prob == 0;
        my $problems = '';
        foreach my $key (split /,|，/, $prob) {
            if(exists $sanitation->{$key}) {
                $problems .= $key . '.(没有做到)' . "$sanitation->{$key}" . ' ';
            } else {
                $problems .= $key;
            }
        }
        $date =~ m!(\d+/\d+/\d+)!;
        $date = $1;
        $date =~ s!/!-!g;
        my %h = (
            'building' => $sheetname,
            'room' => $room,
            'problem' => $problems,
            'date' => $date
            );
        push @list, \%h;
    }
    #    say to_json \@list;
    open JSON_OUT, '>:encoding(UTF-8)', './data/week.json';
    print JSON_OUT to_json \@list;
    close JSON_OUT;
}
